"""项目"""
from datetime import datetime
from collections import OrderedDict
from psycopg2 import extras
import pytz
from flask import Blueprint, request, g
from util.response import response
import dao.project as project_dao
import util.validation as validation

project_api = Blueprint('project_api', __name__)


@project_api.route("/api/project", methods=['POST'])
def store():
    """添加项目"""
    data = request.get_json()
    # 验证参数必填
    _, msg = validation.validate_fields(data, ['project_name', 'project_code', 'start_time', 'room_id'])
    if msg:
        return response(422, msg, {})

    # 验证时间是否能格式化
    _, msg = validation.validate_date_time(data["start_time"])
    if msg:
        return response(422, msg, {})

    # 根据 项目编号 (project_code) 创建时间 (created_at) 查询数据库项目是否已存在并且上次添加时间是否大于24小时
    rowcount = project_dao.project_exists(data["project_code"],
                                int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp()) - 86400)

    if rowcount != 0:
        return response(422, "项目已存在且上次创建时间未大于24小时", {})

    new_project_id = project_dao.insert(data)

    return response(0, "success", {"new_project_id": new_project_id})
   

@project_api.route("/api/project", methods=['GET'])
def index():
    """项目列表"""
    # 获取页数和每页大小
    page_number = request.args.get('page_number', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)
    keyword = request.args.get('keyword', None, type=str)
    project_id = request.args.get('id', None, type=int)
    start_date = request.args.get('start_date', None, type=int)
    end_date = request.args.get('end_date', None, type=int)
    project_status = request.args.get('project_status', None, type=int)

    # 获取项目列表
    result = project_dao.index(page_number, page_size, keyword, project_id, start_date, end_date, project_status)
    total_rows = project_dao.total(keyword, project_id, start_date, end_date, project_status)
    return response(0, "success", {"data": result, **project_dao.page_info(page_number, page_size, total_rows)})


@project_api.route("/api/project/<int:project_id>", methods=['PUT'])
def update(project_id):
    """更新项目"""
    data = request.get_json()
    set_data, params, msg = project_dao.prepare_data_and_params(data)
    if msg:
        return response(422, "参数拼接失败", {})

    with g.db_conn.cursor() as cursor:
        res = project_dao.check_project_exists(cursor, project_id)
        if not res:
            return response(404, "项目不存在", {})
        project_dao.update(cursor, set_data, params, project_id)
    return response(0, "success", {})
    

@project_api.route("/api/project/<int:project_id>", methods=['DELETE'])
def destroy(project_id):
    """删除项目"""
    with g.db_conn.cursor() as cursor:
        res = project_dao.check_project_exists(cursor, project_id)
        if not res:
            return response(500, "项目不存在", {})
        project_dao.delete(cursor, project_id)
    return response(0, "success", {})


@project_api.route("/api/project_date", methods=['GET'])
def project_date():
    """时间序列"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:

        query = """
            SELECT 
                CAST(EXTRACT(year from to_timestamp(created_at)) as integer) y, 
                CAST(EXTRACT(month from to_timestamp(created_at))as integer) m, 
                CAST(EXTRACT(day from to_timestamp(created_at))as integer) d
            FROM 
                projects 
            WHERE deleted_at is null
        """

        cursor.execute(query)

        data = cursor.fetchall()

        result = {}
        for item in sorted(data, key=lambda x: (x['y'], x['m'], x['d'])):
            year = item["y"]
            month = item["m"]
            day = item["d"]

            if year not in result:
                result[year] = OrderedDict()

            if month not in result[year]:
                result[year][month] = []

            if day not in result[year][month]:
                result[year][month].append(day)

    return response(0, 'success', result)
